clear
set more off

global lecmen    "\\intra\partages\au_amic2\SRCV\TABLES_INITIALES\INPUT\MENAGES"
global lecmensas "\\intra\partages\au_amic2\SRCV_AVRIL2021\TABLES_INITIALES_AVRIL2021\MENAGES"
global lecmencsv "\\intra\partages\au_amic2\SRCV_AVRIL2021\TABLES_INITIALES_AVRIL2021\MENAGES"
global lectabpas "\\intra\partages\au_amic2\SRCV_AVRIL2021\TABLES_INITIALES_AVRIL2021\SRCV_2020"
global ecrmen    "\\intra\partages\au_amic2\SRCV_AVRIL2021\BASES_FEV2022_V5\2020\MENAGES"
 

/* 1 - EXTRACTION DES VARIABLES SELECTIONNéES */
/* a - fichier STATA 1ère livraison 2008-2017 */
foreach an of numlist 8  9 {
cd "$lecmen"
use menages0`an'_diff.dta, clear
keep 	men pond_* idmenc_* ///
		hy090n hy040n hy100n hy050n hy070n hy060n hy080n hy130n hy110n hy145n hy120n hy020 db030
gen annee_SRCV = 200`an'
order men annee_SRCV 
cd "$ecrmen"
save men_`an'.dta, replace
display `an'
count 
}

foreach an of numlist 10 / 12 {
cd "$lecmen"
use menages`an'_diff.dta, clear
keep 	men pond_* idmenc_* ///
		hy090n hy040n hy100n hy050n hy070n hy060n hy080n hy130n hy110n hy145n hy120n hy020 db030
gen annee_SRCV = 20`an'
order men annee_SRCV 
cd "$ecrmen"
save men_`an'.dta, replace
display `an'
count 
}

foreach an of numlist 13/17 {
cd "$lecmen"
use menages`an'_diff.dta, clear
keep 	men pond_* idmenc_* ///
		hy090n hy040n hy100n hy050n hy070n hy060n hy080n hy130n hy110n hy145n hy120n hy020 db030
gen annee_SRCV = 20`an'
order men annee_SRCV 
cd "$ecrmen"
save men_`an'.dta, replace
display `an'
count 
}

/* b - fichier SAS 2nde livraison 2018-2019 */
foreach an of numlist 18 / 19 {
cd "$lecmensas"
use menages_20`an'.dta, clear
keep 	men pond_* IDMENC_* ///
		HY090N HY040N HY100N HY050N HY070N HY060N HY080N HY130N HY110N HY145N HY120N HY020 DB030
gen annee_SRCV = 20`an'
order men annee_SRCV 
cd "$ecrmen"
save men_`an'.dta, replace
display `an'
count 
}

/* c - fichier CSV 3ème livraison 2020 */
foreach an of numlist 20 {
cd "$lecmencsv"
use menages_20`an'.dta, clear
keep 	/* men pond_* IDMENC_* */ DB090 ///
		HY090N HY040N HY100N HY050N HY070N HY060N HY080N HY130N HY110N /*HY145N*/ irpp th HY120N HY020 DB030
gen annee_SRCV = 20`an'
order DB030 annee_SRCV 
cd "$ecrmen"
save men_`an'.dta, replace
display `an'
count 
}


/* 2 - CHANGEMENT DE NOM DES VARIABLES + TABLE PASSAGE */
/* a - chgt nom variables */
foreach an of numlist 8 / 17 {
cd "$ecrmen"
use men_`an'.dta, clear
rename hy090n HY090N
rename hy040n HY040N
rename hy100n HY100N
rename hy050n HY050N
rename hy070n HY070N
rename hy060n HY060N
rename hy080n HY080N
rename hy130n HY130N
rename hy110n HY110N
rename hy145n HY145N
rename hy120n HY120N
rename hy020  HY020
rename idmenc_* idmenc
rename pond_* pond_men
save men_`an'.dta, replace
}

foreach an of numlist 18 / 19 {
cd "$ecrmen"
use men_`an'.dta, clear
rename IDMENC_* idmenc
rename pond_* pond_men
rename DB030 db030
save men_`an'.dta, replace
}

foreach an of numlist 20 {
cd "$ecrmen"
use men_`an'.dta, clear
rename DB030 db030
rename irpp IRPP
rename th TH
save men_`an'.dta, replace
}

/* b - table passage */
clear
use "$lectabpas\idmen.dta"
rename DB030 db030
save "$ecrmen\idmen.dta", replace

clear
use "$ecrmen\men_20.dta"
merge m:m db030 using "$ecrmen\idmen.dta"
save "$ecrmen\merge_menages", replace


* on supprime les ménages de tabpass n'existant pas dans SRCV 2020
clear
use "$ecrmen\merge_menages"
drop if _merge == 2
drop _merge
order db030 ssech IDMENC_17 IDMENC_18 men IDMENC_19
order db030 annee_SRCV ssech IDMENC_17 IDMENC_18 men IDMENC_19
sort db030 annee_SRCV ssech IDMENC_17 IDMENC_18 men IDMENC_19
destring db030, replace
save  "$ecrmen\men_20.dta", replace
sort men


/* 3 - EMPILEMENT DES TABLES */
clear 
use "$ecrmen\men_8.dta", clear
append using 	"$ecrmen\men_9.dta"  "$ecrmen\men_10.dta" "$ecrmen\men_11.dta" ///
				"$ecrmen\men_12.dta" "$ecrmen\men_13.dta" "$ecrmen\men_14.dta" ///
				"$ecrmen\men_15.dta" "$ecrmen\men_16.dta" "$ecrmen\men_17.dta" ///
				"$ecrmen\men_18.dta" "$ecrmen\men_19.dta" "$ecrmen\men_20.dta" 
order men annee_SRCV idmenc 
sort men annee_SRCV 				
save "$ecrmen\menages_2008_2020.dta", replace


/* 4 - LABELS */
clear 
use "$ecrmen\menages_2008_2020.dta", clear
label variable men 			"Identifiant longitudinal du menage" 
label variable annee_SRCV	"annee de l'enquete/du fichier"
label variable pond_men		"Ponderation transversale menages"
label variable HY040N		"Revenu net tire de la location de propriete ou terres"
label variable HY050N		"Montant net redresse des alloc. famille/enfants"
label variable HY060N		"Montant net des alloc. d'exclusion sociale non classées ailleurs"
label variable HY070N		"Montant net redresse des aides au logement"
label variable HY080N		"Transferts nets percus entre menages (loyers, pensions)"
label variable HY090N		"Montant annuel net interets, div., benef. tires d'invest. en capital"
label variable HY100N		"Montant annuel net des remboursements interets hypothecaires"
label variable HY110N		"Revenus nets percus par personnes moins 16 ans"
label variable HY120N		"Montant net impot fortune"
label variable HY130N		"Montant net transfert inter-menages verses regulierement en especes"
label variable HY145N		"Rembours./encaissements lies a ajustement d'impot"
label variable HY020		"Revenu total disponible du menage"
label variable db030		"Identifiant européen du ménage"
label variable IRPP 		"IR du menage"
label variable TH   		"Taxe d habitation"
order men annee_SRCV idmenc IDMENC_19 IDMENC_18 IDMENC_17 db030 pond_men 
save "$ecrmen\menages_2008_2020.dta", replace
	
	
/* 5 - REMPLACEMENT DES IDMENC DE 2020 ABSENTS PAR IDMENC_19 ou IDMENC_18 ou IDMENC_17 */
clear 
use "$ecrmen\menages_2008_2020.dta", clear
gen indic_replace = .
replace indic_replace = 19 if (annee_SRCV == 2020 & idmenc == "" & IDMENC_19 != "")
replace idmenc = IDMENC_19 if (annee_SRCV == 2020 & idmenc == "" & IDMENC_19 != "")

replace indic_replace = 18 if (annee_SRCV == 2020 & idmenc == "" & IDMENC_19 == "" & IDMENC_18 != "")
replace idmenc = IDMENC_18 if (annee_SRCV == 2020 & idmenc == "" & IDMENC_19 == "" & IDMENC_18 != "")

replace indic_replace = 17 if (annee_SRCV == 2020 & idmenc == "" & IDMENC_19 == "" & IDMENC_18 == "" & IDMENC_17 != "")
replace idmenc = IDMENC_17 if (annee_SRCV == 2020 & idmenc == "" & IDMENC_19 == "" & IDMENC_18 == "" & IDMENC_17 != "")

order men annee_SRCV idmenc IDMENC_19 IDMENC_18 IDMENC_17 indic_replace db030 pond_men 

tab indic_replace

* NETTOYAGE
drop IDMENC_17 IDMENC_18 IDMENC_19
save "$ecrmen\menages_2008_2020.dta", replace

count if idmenc =="" /* 5595 */
count if men =="" /* 5595 */
count if db030 ==. /* 1 */

clear 
use "$ecrmen\menages_2008_2020.dta", clear
keep if annee_SRCV ==2020
save "$ecrmen\fin_men_2020.dta", replace
